package net.zjitc.summary.student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @Author admin
 * @Date 2019/5/5 8:23
 * 工具类的具体实现类，用JDBC实现
 */
public class StudentDaoDB implements StudentDao {
    Connection conn=JDBCUtil.getConn();
    PreparedStatement pstmt=null;
    ResultSet rs=null;

    @Override
    public int insert(Student student) {
        String sql="insert into student(id,name,sex,age,className)" +
                "values(?,?,?,?,?)";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,student.getId());
            pstmt.setString(2,student.getName());
            pstmt.setString(3,student.getSex());
            pstmt.setInt(4,student.getAge());
            pstmt.setString(5,student.getClassName());
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public ArrayList<Student> selectAll() {
        ArrayList<Student> arrayList=new ArrayList<Student>();
        String sql="select * from student";
        try {
            /* 获得执行SQL语句的patmt对象*/
            pstmt=conn.prepareStatement(sql);
            /* 执行查询语句，结果保存在rs*/
            rs=pstmt.executeQuery();
            /* 将结果集中的内容保存在集合*/
            while (rs.next()){
                String id=rs.getString("id");
                String name=rs.getString("name");
                String sex=rs.getString("sex");
                int age=rs.getInt("age");
                String className=rs.getString("className");
                Student student=new Student(id,name,sex,age,className);
                arrayList.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return arrayList;
    }

    @Override
    public Student selectById(String id) {
        Student student=null;
        String sql="select * from student where id=?";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,id);
            rs=pstmt.executeQuery();
            if(rs.next()){
                String name=rs.getString("name");
                String sex=rs.getString("sex");
                int age=rs.getInt("age");
                String className=rs.getString("className");
                student=new Student(id,name,sex,age,className);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return student;
    }

    @Override
    public int update(Student student) {
        String sql="update student set name=?,sex=?,age=?,className=?" +
                " where id=?";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,student.getName());
            pstmt.setString(2,student.getSex());
            pstmt.setInt(3,student.getAge());
            pstmt.setString(4,student.getClassName());
            pstmt.setString(5,student.getId());
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int delete(String id) {
        String sql="delete from student where id=?";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,id);
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public void close() {
        JDBCUtil.close(rs,pstmt,conn);
    }
}
